package com.augurit.gzcgwsp.state.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jbpm.api.history.HistoryProcessInstance;
import org.jbpm.api.history.HistoryTask;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;

import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.excelimportexport.util.FileUtil;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.state.dao.YwXzXkSpStateDao;
import com.augurit.gzcgwsp.state.service.IYwXzXkSpStateService;
import com.augurit.gzcgwsp.state.web.form.YwRqStateForm;

/**
 * 行政许可审批查询统计service实现类
 * 
 * @author 张军
 * 
 */
@Service
@Transactional
public class YwXzXkSpStateServiceImpl implements IYwXzXkSpStateService {
	private Logger log = Logger.getLogger(YwXzXkSpStateServiceImpl.class);

	@Autowired
	private YwXzXkSpStateDao ywXzXkSpStateDao;

	/**
	 * 分页查询
	 */
	public Page search(Page page, Map map) {
		String sql = this.buildSql(map);
		// List list=this.ywXzXkSpStateDao.searchByPage(sql, page, map);
		page = this.ywXzXkSpStateDao.findPage(sql, page, map);
		// 转换page
		List<YwRqStateForm> list = this.convertFormList(page.getResult(), map);
		page.setResult(list);
		return page;

	}

	/**
	 * 根据查询的结果转换
	 * 
	 * @param list
	 * @return
	 */
	private List<YwRqStateForm> convertFormList(List list, Map params) {
		String queryresult = (String) params.get("result");// 页面查询是否同意不同意
		List<YwRqStateForm> result = new ArrayList();
		if (list != null && list.size() > 0) {
			YwRqStateForm form = null;
			for (int k = 0; k < list.size(); k++) {
				Map map = (Map) list.get(k);
				String sqdw = (String) map.get("bus_memo1");
				String state=(String) map.get("state_");//是否结案
				String templateName = (String) map.get("template_name");
				String file_code = (String) map.get("file_code");
				String slsj = (String) map.get("bus_date2");// 受理时间
				String acceptNo = (String) map.get("bus_memo3");
				String entityId=(String) map.get("wf_bus_instance_id");//流程实例id
				String procinst=(String) map.get("procinst_");
				form = new YwRqStateForm();
				form.setTemplateName(templateName);
				if(slsj.length()>=10){
					slsj=slsj.substring(0,10);
				}
				form.setSlsj(DateUtilsEX.getStringToDate(slsj,"yyyy-MM-dd"));
				form.setSadate(slsj);
				form.setAcceptNo(acceptNo);
				if(StringUtils.isNotBlank(entityId)){
					form.setId(Long.valueOf(entityId));
				}
				form.setSqdw(sqdw);
				if("ended".equals(state)){
				   if("bzcltzs".equals(file_code)){
					  form.setAcceptResult("一次性告知");
				    }else if("zyxzxkjds".equals(file_code)){
				    	 form.setAcceptResult("准予许可");
				    }else if("byxkjds".equals(file_code)){
				    	 form.setAcceptResult("不予许可");
				    }else {
				    	 form.setAcceptResult("其他");
				    }
				}else if("active".equals(state)){
					form.setAcceptResult("办理中");
				}
			   //查询办理进度
			   if("active".equals(state)){
				   String sql="select t.activity_chinese_name from JBPM4_HIST_TASK t where t.state_='open' and  t.procinst_='"+procinst+"'";
				   List data=ywXzXkSpStateDao.queryForList(sql);
				   String activityname="";
				   if (data != null && data.size() > 0) {
						for (int j = 0; j< data.size(); j++) {
							Map nameMap=(Map) data.get(0);
							activityname=(String) nameMap.get("activity_chinese_name");
							form.setProgress(activityname);
							break;
						}
				   }
			   }else{
					form.setProgress("已结案");
			   }
				result.add(form);
			}
		}
		return result;
	}

	/**
	 * 构造查询用的map
	 * 
	 * @param paramMap
	 * @return
	 */
	private Map<String, Object> getMapByParam(Map paramMap) {
		String sldate = (String) paramMap.get("sqdate");
		String sqstartdate=(String) paramMap.get("sqstartdate");
		Map<String, Object> values = new HashMap<String, Object>();
		values.put("procState", HistoryProcessInstance.STATE_ENDED);
		values.put("taskState", HistoryTask.STATE_COMPLETED);
		Date sdate = null;
		Date edate = null;
		if (StringUtils.isNotBlank(sldate)) {//收案结束时间
			sldate = sldate.substring(0, 10);
			String enddate = sldate + " 23:59:59";
			try {
				edate = DateUtilsEX.stringToDate(enddate);
				//values.put("sbusDate2", sdate);
				values.put("ebusDate2", edate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		
		if (StringUtils.isNotBlank(sqstartdate)) {//收案开始时间
			sqstartdate = sqstartdate.substring(0, 10);
			sqstartdate = sqstartdate + " 00:00:00";
			try {
				 sdate = DateUtilsEX.stringToDate(sqstartdate);
				values.put("sbusDate2", sdate);
				//values.put("ebusDate2", edate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		return values;
	}

	/**
	 * 生成hql
	 * 
	 * @param map
	 * @return
	 */
	private String buildSql(Map map) {
		// 查询语句及参数
		String acceptNo = (String) map.get("acceptNo");
		String sldate = (String) map.get("sqdate");
		String sqstartdate=(String) map.get("sqstartdate");
		String cbyw = (String) map.get("cbyw");
		String result = (String) map.get("result");
		String progress=(String) map.get("progress");//查询办理进度
		
		StringBuffer tempHql = new StringBuffer();
		
		if (StringUtils.isNotBlank(result)) {
			if("3".equals(result)){//结案处理结果为其他
				tempHql.append("select t.* \r");
			}else{
				tempHql.append("select t.*,doc.file_code \r");
			}
		}else{
			 tempHql.append("select t.*,doc.file_code \r");
		}
		
		tempHql.append("from(select distinct temp.template_code, ins.bus_date2, ins.bus_date1,ins.bus_memo1,ins.bus_memo3, \r");
		tempHql.append(" temp.template_name,ins.master_entity_key,proc.start_ ,ins.wf_bus_instance_id,proc.state_,task.procinst_ from \r");
		tempHql.append(" JBPM4_HIST_TASK task,JBPM4_HIST_PROCINST proc,WF_BUS_INSTANCE ins,WF_BUS_TEMPLATE temp \r");
		tempHql.append(" where task.procinst_=proc.id_ and proc.id_=ins.proc_inst_id \r");
		tempHql.append(" and ins.template_id=temp.id   \r");
		if(StringUtils.isNotBlank(progress)){
			if("1".equals(progress)){//经办人办理
				tempHql.append("  and  proc.state_='active'  and task.activity_name like 'jbr%' and task.state_='open' \r");
			}else if("2".equals(progress)){//处领导审批
				tempHql.append("  and  proc.state_='active'   and task.activity_name='czgldsh' and task.state_='open'\r");
			}else if("3".equals(progress)){//委领导审批
				tempHql.append("  and  proc.state_='active'  and task.activity_name='wfgldsh' and task.state_='open' \r");
			}else if("4".equals(progress)){//窗口办理
				tempHql.append("  and  proc.state_='active'  and task.activity_name='cksa' and task.state_='open' \r");
			}
			else if("5".equals(progress)){//已经结案
				tempHql.append("  and  proc.state_='ended' \r");
			}
		}else{
			if (StringUtils.isNotBlank(result)) {//审批结果
				tempHql.append("  and proc.state_='ended' \r");
			}else{
				tempHql.append("  and ( proc.state_='ended' or  proc.state_='active') \r");
			}
		}
		if (StringUtils.isNotBlank(cbyw)) {
			tempHql.append(" and temp.template_code='" + cbyw + "'");
		} else {
			tempHql.append(" and temp.template_code in ('rqssgd','rqrsqjazwxqyzz','rqjyxkz_gdqy','rqjyxkz_pzqy','rqssgdqx','rqrsqjazwxqyzzqx','rqjyxkzqx_gdqy','rqjyxkzqx_pzqy')");
		}
		if (StringUtils.isNotBlank(sqstartdate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')>='"
					+ sqstartdate + "'");
		}
		if (StringUtils.isNotBlank(sldate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')<='"
					+ sldate + "'");
		}
		if (StringUtils.isNotBlank(acceptNo)) {
			tempHql.append(" and  ins.bus_memo3='" + acceptNo + "'");
		}
		tempHql.append("   order by proc.start_ desc \r");

		if (StringUtils.isNotBlank(result)) {
			if ("0".equals(result)) {
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code ='byxkjds' \r");
			} else if ("1".equals(result)) {
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code='zyxzxkjds' \r");
			}else if("2".equals(result)){//一次性告知要去掉同意的数据
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code='bzcltzs' \r");
				tempHql.append(" and doc.yw_id not in (select ts.yw_id from yw_print_doc ts where ");
				tempHql.append(" (ts.file_code='zyxzxkjds'or ts.file_code='byxkjds')) ");
				
			}else if("3".equals(result)){//其他，最后环节制作文书为空的
				tempHql.append(")  t where not exists  (select doc.id from YW_PRINT_DOC doc where doc.yw_id=t.master_entity_key \r");
				tempHql.append(" and doc.template_code=t.template_code and doc.file_code in ('byxkjds','bzcltzs','zyxzxkjds'))\r");
				
			}
		} else {
			tempHql.append(") t left join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
			tempHql.append(" and ( doc.file_code in ('byxkjds','zyxzxkjds') \r");
			tempHql.append(" or  (doc.file_code='bzcltzs' and doc.yw_id not in  \r");
			tempHql.append(" (select ts.yw_id from yw_print_doc ts where \r");
			tempHql.append(" (ts.file_code='zyxzxkjds'or ts.file_code='byxkjds')) \r");
			tempHql.append(" ) \r");
			tempHql.append(" ) \r");
			
		}
		return tempHql.toString();
	}

	/*
	 * 导出查询excel
	 * 
	 * @see com.augurit.gzcgwsp.state.service.IYwXzXkSpStateService#exportExcel(java.util.Map)
	 */
	public String exportExcel(Map map) {
		String path = (String) map.get("contextpath");
		String outFilePath = GetFileName.GetRealPath("Templates/modelTemplate",
				"xls");
		String outPath = path + File.separator + outFilePath;
		outPath = outPath.replace("/", File.separator);
		String dir = outPath.substring(0, outPath.lastIndexOf(File.separator));
		dir = dir.replace("/", File.separator);
		File dirfile = new File(dir);
		if (!dirfile.exists()) {
			dirfile.mkdirs();
		}
		String inFilePath = path + File.separator + "Templates"
				+ File.separator + "exportRqData.xls";
		outPath = outPath.replace("/", File.separator);
		Map<String, Object> values = getMapByParam(map);
		String sql = buildSql(map);
		List list = ywXzXkSpStateDao.queryForList(sql);
		writeData(inFilePath, outPath, list, map);
		String filename = outPath.substring(
				outPath.lastIndexOf(File.separator) + 1, outPath.length());
		try {// 删除其他文件
			FileUtil.delDir(dir, filename);
		} catch (IOException e) {
			log.error("删除文件错误" + e);
		}
		return outFilePath;
	}

	/*
	 * 导出统计excel
	 * 
	 * @see com.augurit.gzcgwsp.state.service.IYwXzXkSpStateService#exportExcel(java.util.Map)
	 */
	public String exportStateExcel(Map map) {
		String path = (String) map.get("contextpath");
		String outFilePath = GetFileName.GetRealPath("Templates/modelTemplate",
				"xls");
		String outPath = path + File.separator + outFilePath;
		outPath = outPath.replace("/", File.separator);
		String dir = outPath.substring(0, outPath.lastIndexOf(File.separator));
		dir = dir.replace("/", File.separator);
		File dirfile = new File(dir);
		if (!dirfile.exists()) {
			dirfile.mkdirs();
		}
		String inFilePath = path + File.separator + "Templates"
				+ File.separator + "exportRqStateData.xls";
		outPath = outPath.replace("/", File.separator);
		YwRqStateForm form=this.searchInfo(map);
		this.writerDataToExcel(inFilePath, outPath, form,map);
	
		//writeStateData(inFilePath, outPath,form, map);
		String filename = outPath.substring(
				outPath.lastIndexOf(File.separator) + 1, outPath.length());
		try {// 删除其他文件
			FileUtil.delDir(dir, filename);
		} catch (IOException e) {
			log.error("删除文件错误" + e);
		}
	
		return outFilePath;
	}
	
	
	private void setCellData(HSSFSheet hssfsheet,HSSFCellStyle cs,int rows,int cols,String content){
		  HSSFRow ssrow = hssfsheet.getRow((short) rows);
          HSSFCell cell = ssrow.getCell((short) cols);
          cell.setCellStyle(cs);
          cell.setCellValue(content);
	}
	
	 /**
     * 写入日期名称信息
     * @param inFilePath
     * @param outFilePath
     * @param entity
     * @return
     */
    private String writerDataToExcel(String inFilePath,String outFilePath,YwRqStateForm form,Map map){
      
      	  try {
      		  FileUtil.copyFile(inFilePath, outFilePath);
			  HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(outFilePath));
			  HSSFSheet hssfsheet = workbook.getSheetAt(0);// 第一个工作表
				int row=3;
				int col=2;
				int col1=col+1;
				int col2=col+2;
				int col3=col+3;
				int col4=col+4;
				String starttime=(String) map.get("statetime")==null?DateUtilsEX.getCurYear()+
						"-01-01":(String)map.get("stattime");
				String endtime=(String) map.get("endtime")==null?DateUtilsEX.getCurYear()+"-"+
						DateUtilsEX.getCurMonth()+"-"+DateUtilsEX.getCurDay():(String)map.get("endtime");
				String title="(统计时间："+starttime+"~"+endtime+")";
	          HSSFCellStyle cs = workbook.createCellStyle();   
	          cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中   
	          setCellData(hssfsheet, cs, 1, 0, title);
	          setCellData(hssfsheet, cs, row, col, String.valueOf(form.getJyxkgd_num()));
	          setCellData(hssfsheet, cs, row, col1, String.valueOf(form.getJyxkpz_num()));
	          setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getQyzz_num()));
	          setCellData(hssfsheet, cs, row, col3, String.valueOf(form.getSsgd_num()));
	          setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getCount_num()));
		      	row++;
				//同意
			  setCellData(hssfsheet, cs, row, col,String.valueOf(form.getJyxkgd_Agreenum()));
		      setCellData(hssfsheet, cs, row, col1, String.valueOf(form.getJyxkpz_Agreenum()));
	          setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getQyzz_Agreenum()));
		      setCellData(hssfsheet, cs, row, col3, String.valueOf(form.getSsgd_Agreenum()));
		      setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getAgreeCountnum()));
				row++;
				//比例
			  setCellData(hssfsheet, cs, row, col,String.valueOf(form.getJyxkgdpercentnum()));
			  setCellData(hssfsheet, cs, row, col1,String.valueOf(form.getJyxkpzpercentnum()));
			  setCellData(hssfsheet, cs, row, col2,String.valueOf(form.getQyzzpercetnum()));
			  setCellData(hssfsheet, cs, row, col3,String.valueOf(form.getSsgdpercentnum()));
			  setCellData(hssfsheet, cs, row, col4,String.valueOf(form.getAgreePercentnum()));
				row++;
				//不同意
				setCellData(hssfsheet,cs,  row,col, String.valueOf(form.getNotjyxkgd_num()));
				setCellData(hssfsheet,cs,  row,col1,String.valueOf(form.getNotjyxkpz_num()));
				setCellData(hssfsheet, cs, row,col2,String.valueOf(form.getNotqyzz_num()));
				setCellData(hssfsheet, cs, row,col3,String.valueOf(form.getNotssgd_num()));
				setCellData(hssfsheet, cs, row,col4, String.valueOf(form.getNotcount_num()));
				row++;
				//比例
				setCellData(hssfsheet, cs,row, col, String.valueOf(form.getJyxkgdNotAgreepercentnum()));
				setCellData(hssfsheet, cs, row, col1,String.valueOf(form.getJyxkpzNotAgreepercentnum()));
				setCellData(hssfsheet, cs, row, col2,String.valueOf(form.getQyzzNotAgreepercetnum()));
				setCellData(hssfsheet, cs, row,col3, String.valueOf(form.getSsgdNotAgreepercentnum()));
				setCellData(hssfsheet, cs, row, col4,String.valueOf(form.getNoAgreetPercentnum()));
				row++;
				//一次性告知
				setCellData(hssfsheet,cs,  row,col, String.valueOf(form.getJyxkgd_gznum()));
				setCellData(hssfsheet,cs,  row,col1,String.valueOf(form.getJyxkpz_gznum()));
				setCellData(hssfsheet, cs, row,col2,String.valueOf(form.getQyzz_gznum()));
				setCellData(hssfsheet, cs, row,col3,String.valueOf(form.getSsgd_gznum()));
				setCellData(hssfsheet, cs, row,col4, String.valueOf(form.getGzCountnum()));
				row++;
				//比例
				setCellData(hssfsheet, cs,row, col, String.valueOf(form.getJyxkgdGzpercentnum()));
				setCellData(hssfsheet, cs, row, col1,String.valueOf(form.getJyxkpzGzpercentnum()));
				setCellData(hssfsheet, cs, row, col2,String.valueOf(form.getQyzzGzpercetnum()));
				setCellData(hssfsheet, cs, row,col3, String.valueOf(form.getSsgdGzpercentnum()));
				setCellData(hssfsheet, cs, row, col4,String.valueOf(form.getGztotalPercentnum()));
				row++;
				//按期完成(宗)
				setCellData(hssfsheet, cs, row, col,String.valueOf(form.getNooverjyxkgd_num()));
				setCellData(hssfsheet, cs,row, col1,String.valueOf(form.getNooverjyxkpz_num()));
				setCellData(hssfsheet, cs, row, col2,String.valueOf(form.getNooverqyzz_num()));
				setCellData(hssfsheet, cs, row, col3,String.valueOf(form.getNooverssgd_num()));
				setCellData(hssfsheet, cs,row, col4, String.valueOf(form.getNoovercount_num()));
				row++;
				//比例
				setCellData(hssfsheet, cs, row, col, String.valueOf(form.getOntimejyxkgdpercentnum()));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(form.getOntimejyxkpzpercentnum()));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getOntimeqyzzpercetnum()));
				setCellData(hssfsheet, cs, row, col3,String.valueOf(form.getOntimessgdpercentnum()));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getOntimepercettotalnum()));
				row++;
				//延期完成(宗)
				setCellData(hssfsheet, cs, row, col, String.valueOf(form.getJyxkgd_num()-form.getNooverjyxkgd_num()-form.getOverjyxkgd_num()));
				setCellData(hssfsheet, cs,row, col1, String.valueOf(form.getJyxkpz_num()-form.getNooverjyxkpz_num()-form.getOverjyxkpz_num()));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getQyzz_num()-form.getNooverqyzz_num()-form.getOverqyzz_num()));
				setCellData(hssfsheet, cs, row, col3, String.valueOf(form.getSsgd_num()-form.getNooverssgd_num()-form.getOverssgd_num()));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getCount_num()-form.getNoovercount_num()-form.getOvercount_num()));
				row++;
				//比例
				setCellData(hssfsheet, cs, row, col,String.valueOf(100-form.getOvertimejyxkgdpercentnum()-form.getOntimejyxkgdpercentnum()));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(100-form.getOvertimejyxkpzpercentnum()-form.getOntimejyxkpzpercentnum()));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(100-form.getOvertimeqyzzpercetnum()-form.getOntimeqyzzpercetnum()));
				setCellData(hssfsheet, cs, row, col3, String.valueOf(100-form.getOvertimessgdpercentnum()-form.getOntimessgdpercentnum()));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(100-form.getOvertimepercettotalnum()-form.getOntimepercettotalnum()));
				row++;
				//超期完成(宗)
				setCellData(hssfsheet, cs, row, col, String.valueOf(form.getOverjyxkgd_num()));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(form.getOverjyxkpz_num()));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getOverqyzz_num()));
				setCellData(hssfsheet, cs,row, col3, String.valueOf(form.getOverssgd_num()));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getOvercount_num()));
				row++;
				//比例
				setCellData(hssfsheet, cs,row, col,String.valueOf(form.getOvertimejyxkgdpercentnum()));
				setCellData(hssfsheet, cs,row, col1, String.valueOf(form.getOvertimejyxkpzpercentnum()));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getOvertimeqyzzpercetnum()));
				setCellData(hssfsheet, cs, row, col3, String.valueOf(form.getOvertimessgdpercentnum()));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getOvertimepercettotalnum()));
				row++;
				//市(宗)
				setCellData(hssfsheet, cs,row, col, String.valueOf(form.getJyxkgd_num()));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(form.getJyxkpz_num()));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(form.getQyzz_num()));
				setCellData(hssfsheet, cs, row, col3,String.valueOf(form.getSsgd_num()));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(form.getCount_num()));
				row++;
				//比例暂时不统计区
				setCellData(hssfsheet, cs, row, col, String.valueOf(100.0));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(100.0));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(100.0));
				setCellData(hssfsheet, cs, row, col3, String.valueOf(100.0));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(100.0));
				row++;
				//区
				setCellData(hssfsheet, cs, row, col, String.valueOf(0));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(0));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(0));
				setCellData(hssfsheet, cs, row, col3, String.valueOf(0));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(0));
				row++;
				//比例
				setCellData(hssfsheet, cs, row, col,String.valueOf(0.0));
				setCellData(hssfsheet, cs, row, col1, String.valueOf(0.0));
				setCellData(hssfsheet, cs, row, col2, String.valueOf(0.0));
				setCellData(hssfsheet, cs, row, col3, String.valueOf(0.0));
				setCellData(hssfsheet, cs, row, col4, String.valueOf(0.0));
				row++;
			  //HSSFRow hssfrow = hssfsheet.createRow(row);// 创建一个行对象;
			  //HSSFCell cell1=hssfrow.createCell((short)col); //创建一个单元格  HSSFCell类表示单元格  注册地址
			  //cell1.setCellValue(entity.getJyxkgd_num());  //设置单元格内容 
			  FileOutputStream fos = new FileOutputStream(outFilePath);
			  
			  workbook.write(fos);
			  fos.close();
			
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return outFilePath;
    }
    
	
	/**
	 * 写入统计数据
	 * 
	 * @param inFilePath
	 * @param outFilePath
	 * @param list
	 */
	private void writeStateData(String inFilePath, String outFilePath, YwRqStateForm form,
			Map map) {
		WritableWorkbook wwb;
		try {
			FileUtil.copyFile(inFilePath, outFilePath);
			//wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
			//Workbook wb=Workbook.getWorkbook(new File(outFilePath)); 
			 Workbook rwb = Workbook.getWorkbook(new File(outFilePath));    
			//打开一个文件的副本，并且指定数据写回到原文件         
			 wwb = Workbook.createWorkbook(new File(outFilePath),rwb);//copy    
			 WritableSheet ws = wwb.getSheet(0);
			WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
					12);
			WritableCellFormat wcf = new WritableCellFormat(font,
					NumberFormats.TEXT);
			wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
			wcf.setAlignment(Alignment.CENTRE);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setWrap(true);
			String starttime=(String) map.get("statetime")==null?DateUtilsEX.getCurYear()+
					"-01-01":(String)map.get("stattime");
			String endtime=(String) map.get("endtime")==null?DateUtilsEX.getCurYear()+"-"+
					DateUtilsEX.getCurMonth()+"-"+DateUtilsEX.getCurDay():(String)map.get("endtime");
			String title="(统计时间："+starttime+"~"+endtime+")";
			ExcelUtil.modiStrCell2(ws, 0, 1, title, wcf);// 标题
				//燃气管道经营许可审批
				int row=3;
				int col=2;
				int col1=col+1;
				int col2=col+2;
				int col3=col+3;
				int col4=col+4;
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getJyxkgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getJyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getQyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getSsgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getCount_num()), wcf);
				row++;
				//同意
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getJyxkpz_num()-form.getNotjyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getJyxkpz_num()-form.getNotjyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getQyzz_num()-form.getNotqyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getSsgd_num()-form.getNotssgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getCount_num()-form.getNotcount_num()), wcf);
				
				row++;
				//比例
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getJyxkgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getJyxkpzpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getQyzzpercetnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getSsgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getPercettotalnum()), wcf);
				row++;
				//不同意
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getNotjyxkgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getNotjyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getNotqyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getNotssgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getNotcount_num()), wcf);
				row++;
				//比例
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(100-form.getJyxkgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(100-form.getJyxkpzpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(100-form.getQyzzpercetnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(100-form.getSsgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(100-form.getPercettotalnum()), wcf);
				row++;
				//按期完成(宗)
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getNooverjyxkgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getNooverjyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getNooverqyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getNooverssgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getNoovercount_num()), wcf);
				row++;
				//比例
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getOntimejyxkgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getOntimejyxkpzpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getOntimeqyzzpercetnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getOntimessgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getOntimepercettotalnum()), wcf);
				row++;
				//延期完成(宗)
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getJyxkgd_num()-form.getNooverjyxkgd_num()-form.getOverjyxkgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getJyxkpz_num()-form.getNooverjyxkpz_num()-form.getOverjyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getQyzz_num()-form.getNooverqyzz_num()-form.getOverqyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getSsgd_num()-form.getNooverssgd_num()-form.getOverssgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getCount_num()-form.getNoovercount_num()-form.getOvercount_num()), wcf);
				row++;
				//比例
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(100-form.getOvertimejyxkgdpercentnum()-form.getOntimejyxkgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(100-form.getOvertimejyxkpzpercentnum()-form.getOntimejyxkpzpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(100-form.getOvertimeqyzzpercetnum()-form.getOntimeqyzzpercetnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(100-form.getOvertimessgdpercentnum()-form.getOntimessgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(100-form.getOvertimepercettotalnum()-form.getOntimepercettotalnum()), wcf);
				row++;
				//超期完成(宗)
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getOverjyxkgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getOverjyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getOverqyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getOverssgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getOvercount_num()), wcf);
				row++;
				//比例
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getOvertimejyxkgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getOvertimejyxkpzpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getOvertimeqyzzpercetnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getOvertimessgdpercentnum()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getOvertimepercettotalnum()), wcf);
				row++;
				//市(宗)
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(form.getJyxkgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(form.getJyxkpz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(form.getQyzz_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(form.getSsgd_num()), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(form.getCount_num()), wcf);
				row++;
				//比例暂时不统计区
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(100.0), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(100.0), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(100.0), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(100.0), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(100.0), wcf);
				row++;
				//区
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(0), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(0), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(0), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(0), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(0), wcf);
				row++;
				//比例
				ExcelUtil.modiStrCell2(ws, col, row, String.valueOf(0.0), wcf);
				ExcelUtil.modiStrCell2(ws, col1, row, String.valueOf(0.0), wcf);
				ExcelUtil.modiStrCell2(ws, col2, row, String.valueOf(0.0), wcf);
				ExcelUtil.modiStrCell2(ws, col3, row, String.valueOf(0.0), wcf);
				ExcelUtil.modiStrCell2(ws, col4, row, String.valueOf(0.0), wcf);
				row++;
				//ws.setProtected(true);
				 wwb.write();    
		           wwb.close();    
		} catch (Exception e) {

		}
	}
	
	
	/**
	 * 写入查询数据
	 * 
	 * @param inFilePath
	 * @param outFilePath
	 * @param list
	 */
	private void writeData(String inFilePath, String outFilePath, List list,
			Map params) {
		WritableWorkbook wwb;
		try {
			wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
			WritableSheet ws = ExcelUtil.getWritableSheetByIndex(wwb, 0);
			WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
					12);
			WritableCellFormat wcf = new WritableCellFormat(font,
					NumberFormats.TEXT);
			wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
			wcf.setAlignment(Alignment.CENTRE);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setWrap(true);
			for (int i = 0; i < list.size(); i++) {
				List<YwRqStateForm> datas = this.convertFormList(list, params);
				try {
					for (YwRqStateForm form : datas) {
						String acceptNo = form.getAcceptNo();// 申请号
						String cpyw = form.getTemplateName();// 审批项目
						String sldate = DateUtilsEX.dateFormate(form.getSlsj(),
								"yyyy-MM-dd");// 受理日期
						String result = form.getAcceptResult();// 结果
						ExcelUtil.modiStrCell2(ws, 0, i + 2, acceptNo, wcf);// 申请号
						ExcelUtil.modiStrCell2(ws, 1, i + 2, cpyw, wcf);// 审批项目
						ExcelUtil.modiStrCell2(ws, 2, i + 2, "广州市城市管理委员会燃气处",
								wcf);// 批准单位
						ExcelUtil.modiStrCell2(ws, 3, i + 2, sldate, wcf);// 受理时间
						ExcelUtil.modiStrCell2(ws, 4, i + 2, result, wcf);// 结果
						ws.setRowView(i + 2, 400);
						i++;
					}
				} catch (Exception e) {
					log.error("导出数据错误" + e);
				}

			}
			wwb.write();
			wwb.close();
		} catch (Exception e) {

		}
	}

	/**
	 * 
	 */
	public YwRqStateForm searchInfo(Map map) {
		YwRqStateForm form = new YwRqStateForm();
		// 查询语句及参数
		List list = this.getTotalCount(map, form);
		try {
			//设置不数量
			getCountInfo(map, form);
			setPercentNum(form);//百分比
			stateOverTimeData(map, form);
			setOverTimePercentNum(form);
			
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return form;
	}

	/**
	 * 设置按时超时比例
	 * 
	 * @param form
	 * @date 2012-3-25
	 */
	private void setOverTimePercentNum(YwRqStateForm form) {
		BigDecimal ontimepercettotalnum = new BigDecimal(form
				.getNoovercount_num());
		ontimepercettotalnum = ontimepercettotalnum.divide(new BigDecimal(form
				.getCount_num()), 4, BigDecimal.ROUND_HALF_UP);// 总的按时比例
		form.setOntimepercettotalnum(ontimepercettotalnum.doubleValue() * 100);

		BigDecimal ontimeqyzzpercetnum = new BigDecimal(form
				.getNooverqyzz_num());
		ontimeqyzzpercetnum = ontimeqyzzpercetnum.divide(new BigDecimal(form
				.getQyzz_num()), 4, BigDecimal.ROUND_HALF_UP);// 企业资质的按时比例
		form.setOntimeqyzzpercetnum(ontimeqyzzpercetnum.doubleValue() * 100);
		
		BigDecimal ontimejyxkgdpercentnum = new BigDecimal(form
				.getNooverjyxkgd_num());
		if(form.getJyxkgd_num()>0){
			ontimejyxkgdpercentnum = ontimejyxkgdpercentnum.divide(new BigDecimal(form
					.getJyxkgd_num()), 4, BigDecimal.ROUND_HALF_UP);// 经营许可管道
		}
		form.setOntimejyxkgdpercentnum(ontimejyxkgdpercentnum.doubleValue() * 100);
		
		BigDecimal ontimejyxkpzpercentnum = new BigDecimal(form
				.getNooverjyxkpz_num());
		ontimejyxkpzpercentnum = ontimejyxkpzpercentnum.divide(new BigDecimal(form
				.getJyxkpz_num()), 4, BigDecimal.ROUND_HALF_UP);// 经营许可液化
		form.setOntimejyxkpzpercentnum(ontimejyxkpzpercentnum.doubleValue() * 100);
		
		BigDecimal ontimessgdpercentnum = new BigDecimal(form
				.getNooverssgd_num());
		ontimessgdpercentnum = ontimessgdpercentnum.divide(new BigDecimal(form
				.getSsgd_num()), 4, BigDecimal.ROUND_HALF_UP);// 设施改动
		form.setOntimessgdpercentnum(ontimessgdpercentnum.doubleValue() * 100);
		
		BigDecimal overtimepercettotalnum = new BigDecimal(form
				.getOvercount_num());
		overtimepercettotalnum = overtimepercettotalnum.divide(new BigDecimal(form
				.getCount_num()), 4, BigDecimal.ROUND_HALF_UP);// 总的超时比例
		form.setOvertimepercettotalnum(overtimepercettotalnum.doubleValue() * 100);

		BigDecimal overtimeqyzzpercetnum = new BigDecimal(form
				.getOverqyzz_num());
		overtimeqyzzpercetnum = overtimeqyzzpercetnum.divide(new BigDecimal(form
				.getQyzz_num()), 4, BigDecimal.ROUND_HALF_UP);// 企业资质的比例
		form.setOvertimeqyzzpercetnum(overtimeqyzzpercetnum.doubleValue() * 100);
		
		BigDecimal overtimejyxkgdpercentnum = new BigDecimal(form
				.getOverjyxkgd_num());
		if(form.getJyxkgd_num()>0){
			overtimejyxkgdpercentnum = overtimejyxkgdpercentnum.divide(new BigDecimal(form
				.getJyxkgd_num()), 4, BigDecimal.ROUND_HALF_UP);// 经营许可管道
		}
		form.setOvertimejyxkgdpercentnum(overtimejyxkgdpercentnum.doubleValue() * 100);
		
		BigDecimal overtimejyxkpzpercentnum = new BigDecimal(form
				.getOverjyxkpz_num());
		overtimejyxkpzpercentnum = overtimejyxkpzpercentnum.divide(new BigDecimal(form
				.getJyxkpz_num()), 4, BigDecimal.ROUND_HALF_UP);// 经营许可液化
		form.setOvertimejyxkpzpercentnum(overtimejyxkpzpercentnum.doubleValue() * 100);
		
		BigDecimal overimessgdpercentnum = new BigDecimal(form
				.getOverssgd_num());
		overimessgdpercentnum = overimessgdpercentnum.divide(new BigDecimal(form
				.getSsgd_num()), 4, BigDecimal.ROUND_HALF_UP);// 设施改动
		form.setOvertimessgdpercentnum(overimessgdpercentnum.doubleValue() * 100);


	}

	/**
	 * 统计按时超时的数据
	 * 
	 * @date 2012-3-25
	 */
	private void stateOverTimeData(Map map, YwRqStateForm form) {
		Map timeMap = new HashMap();// 构造不超时
		timeMap.putAll(map);
		timeMap.put("overtime", "overtime");
		String overtimeSql = this.baseSql(timeMap, true);
		List overtimeData = this.ywXzXkSpStateDao.queryForList(overtimeSql);
		int notovertimeCount = 0;
		int overtimeCount = 0;
		if (overtimeData != null && overtimeData.size() > 0) {
			for (int i = 0; i < overtimeData.size(); i++) {
				Map data = (Map) overtimeData.get(i);
				String tempCode = (String) data.get("template_code");
				String timetype = (String) data.get("timetype");
				String countnum = (String) data.get("countnum") == null ? "0"
						: (String) data.get("countnum");
				if ("rqjyxkz_pzqy".equals(tempCode)) {
					if ("notovertime".equals(timetype)) {
						form.setNooverjyxkpz_num(Integer.valueOf(countnum));
						notovertimeCount += Integer.valueOf(countnum);
					} else {
						form.setOverjyxkpz_num(Integer.valueOf(countnum));
						overtimeCount += Integer.valueOf(countnum);
						overtimeCount += Integer.valueOf(countnum);
					}
				} else if ("rqssgd".equals(tempCode)) {
					if ("notovertime".equals(timetype)) {
						form.setNooverssgd_num(Integer.valueOf(countnum));
						notovertimeCount += Integer.valueOf(countnum);
					} else {
						form.setOverssgd_num(Integer.valueOf(countnum));
						overtimeCount += Integer.valueOf(countnum);
					}
				} else if ("rqjyxkz_gdqy".equals(tempCode)) {
					if ("notovertime".equals(timetype)) {
						form.setNooverjyxkgd_num(Integer.valueOf(countnum));
						notovertimeCount += Integer.valueOf(countnum);
					} else {
						form.setOverjyxkgd_num(Integer.valueOf(countnum));
						overtimeCount += Integer.valueOf(countnum);
					}

				} else if ("rqrsqjazwxqyzz".equals(tempCode)) {
					if ("notovertime".equals(timetype)) {
						form.setNooverqyzz_num(Integer.valueOf(countnum));
						notovertimeCount += Integer.valueOf(countnum);
					} else {
						form.setOverqyzz_num(Integer.valueOf(countnum));
						overtimeCount += Integer.valueOf(countnum);
					}

				}
			}
		}
		form.setOvercount_num(overtimeCount);
		form.setNoovercount_num(notovertimeCount);

	}

	/**
	 * 四舍五入改变值
	 * @param value
	 * @return
	 */
	private double changVal(BigDecimal value){
		double percentnum = value.doubleValue()*100;
		 DecimalFormat df=new DecimalFormat(".0");
		return Double.valueOf(df.format(percentnum));
	}
	/**
	 * 设置百分比
	 * 
	 * @param form
	 * @date 2012-3-24
	 */
	private void setPercentNum(YwRqStateForm form) {
		if(form.getJyxkgd_Agreenum()>0){
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getJyxkgd_Agreenum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getJyxkgd_num()), 3, BigDecimal.ROUND_HALF_UP);// 管道同意比例
			form.setJyxkgdpercentnum(this.changVal(jyxkgdpercentnum));
		}
		if(form.getNotjyxkgd_num()>0){//不同意比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getNotjyxkgd_num()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getJyxkgd_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setJyxkgdNotAgreepercentnum(this.changVal(jyxkgdpercentnum));
		}
		if(form.getJyxkgd_gznum()>0){//一次性告知比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getJyxkgd_gznum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getJyxkgd_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setJyxkgdGzpercentnum(this.changVal(jyxkgdpercentnum));
		}
		
		//液化气
		if(form.getJyxkpz_Agreenum()>0){
			BigDecimal jyxkpzpercentnum = (new BigDecimal(form.getJyxkpz_Agreenum()));
			jyxkpzpercentnum = jyxkpzpercentnum.divide(new BigDecimal(form
					.getJyxkpz_num()), 3, BigDecimal.ROUND_HALF_UP);// 液化比例
			form.setJyxkpzpercentnum(this.changVal(jyxkpzpercentnum));
		}
		if(form.getNotjyxkgd_num()>0){//不同意比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getNotjyxkgd_num()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getJyxkgd_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setJyxkgdNotAgreepercentnum(this.changVal(jyxkgdpercentnum));
		}
		
		if(form.getJyxkpz_gznum()>0){//一次性告知比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getJyxkpz_gznum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getJyxkpz_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setJyxkpzGzpercentnum(this.changVal(jyxkgdpercentnum));
		}
		
		BigDecimal ssgdpercentnum = (new BigDecimal(form.getSsgd_num())
				.subtract(new BigDecimal(form.getNotssgd_num())));// 设施改动比例
		ssgdpercentnum = ssgdpercentnum.divide(new BigDecimal(form
				.getSsgd_num()), 4, BigDecimal.ROUND_HALF_UP);
		form.setSsgdpercentnum(this.changVal(ssgdpercentnum));

		//设施改动
		if(form.getSsgd_Agreenum()>0){
			BigDecimal jyxkpzpercentnum = (new BigDecimal(form.getSsgd_Agreenum()));
			jyxkpzpercentnum = jyxkpzpercentnum.divide(new BigDecimal(form
					.getSsgd_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setSsgdpercentnum(this.changVal(jyxkpzpercentnum));
		}
		if(form.getNotssgd_num()>0){//不同意比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getNotssgd_num()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getSsgd_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setSsgdNotAgreepercentnum(this.changVal(jyxkgdpercentnum));
		}
		
		if(form.getSsgd_gznum()>0){//一次性告知比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getSsgd_gznum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getSsgd_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setSsgdGzpercentnum(this.changVal(jyxkgdpercentnum));
		}
	
		
		// 企业资质比例
		if(form.getQyzz_Agreenum()>0){
			BigDecimal jyxkpzpercentnum = (new BigDecimal(form.getQyzz_Agreenum()));
			jyxkpzpercentnum = jyxkpzpercentnum.divide(new BigDecimal(form
					.getQyzz_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setQyzzpercetnum(this.changVal(jyxkpzpercentnum));
		}
		if(form.getNotqyzz_num()>0){//不同意比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getNotqyzz_num()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getQyzz_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setQyzzNotAgreepercetnum(this.changVal(jyxkgdpercentnum));
		}
		
		if(form.getQyzz_gznum()>0){//一次性告知比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getQyzz_gznum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getQyzz_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setQyzzGzpercetnum(this.changVal(jyxkgdpercentnum));
		}
		
		if(form.getGzCountnum()>0){//一次性告知比例
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getGzCountnum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getCount_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setGztotalPercentnum(this.changVal(jyxkgdpercentnum));
		}
		if(form.getAgreeCountnum()>0){//同意
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getAgreeCountnum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getCount_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setAgreePercentnum(this.changVal(jyxkgdpercentnum));
		}
		if(form.getNotAgreeNum()>0){//
			BigDecimal jyxkgdpercentnum = (new BigDecimal(form.getNotAgreeNum()));
			jyxkgdpercentnum = jyxkgdpercentnum.divide(new BigDecimal(form
					.getCount_num()), 3, BigDecimal.ROUND_HALF_UP);
			form.setNoAgreetPercentnum(this.changVal(jyxkgdpercentnum));
		}
		
//		form.setQyzzpercetnum(this.changVal(qyzzpercetnum));
//		BigDecimal accepttotalnum = (new BigDecimal(form.getCount_num())
//				.subtract(new BigDecimal(form.getNotcount_num())));
//		accepttotalnum = accepttotalnum.divide(new BigDecimal(form
//				.getCount_num()), 4, BigDecimal.ROUND_HALF_UP);// 总数同意的比例
//		form.setPercettotalnum(this.changVal(accepttotalnum));

	}

	/**
	 * 查询出总和
	 * 
	 * @return
	 */
	private List getTotalCount(Map map, YwRqStateForm form) {
		String sql = this.baseSql(map, true);
		List list = this.ywXzXkSpStateDao.queryForList(sql);
		int totalCount = 0;
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				Map data = (Map) list.get(i);
				String tempCode = (String) data.get("template_code");
				String countnum = (String) data.get("countnum") == null ? "0"
						: (String) data.get("countnum");
				if ("rqjyxkz_pzqy".equals(tempCode)) {
					totalCount += Integer.valueOf(countnum);
					form.setJyxkpz_num(Integer.valueOf(countnum));
				} else if ("rqssgd".equals(tempCode)) {
					form.setSsgd_num(Integer.valueOf(countnum));
					totalCount += Integer.valueOf(countnum);
				} else if ("rqjyxkz_gdqy".equals(tempCode)) {
					form.setJyxkgd_num(Integer.valueOf(countnum));
					totalCount += Integer.valueOf(countnum);
				} else if ("rqrsqjazwxqyzz".equals(tempCode)) {
					form.setQyzz_num(Integer.valueOf(countnum));
					totalCount += Integer.valueOf(countnum);
				}
			}
		} else {
			form.setJyxkpz_num(0);
			form.setSsgd_num(0);
			form.setJyxkgd_num(0);
			form.setQyzz_num(0);
		}
		form.setCount_num(totalCount);
		return list;
	}

	/**
	 * 查询每种业务同意不同意的数目
	 * 
	 * @param map
	 * @return
	 */
	private List getCountInfo(Map map, YwRqStateForm form) {
		String sql = this.baseSql(map, false);
		StringBuffer sb = new StringBuffer();
		sb.append("select count(1) as countnum,t.template_code,doc.file_code from ( \r");
		sb.append(sql);
		sb.append(") t left join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and \r");
		sb.append(" doc.template_code=t.template_code  and doc.file_code in ('byxkjds','bzcltzs','zyxzxkjds')  \r");
		sb.append(" group by doc.file_code,t.template_code \r");
		List list = this.ywXzXkSpStateDao.queryForList(sb.toString());
		int agreecount_num = 0;
		int notAgreecount=0;
		int gzCount=0;
		if (list != null && list.size() > 0) {//如果没有制文，全部当通过的审批
			for (int i = 0; i < list.size(); i++) {
				Map data = (Map) list.get(i);
				String tempCode = (String) data.get("template_code");
				String countnum = (String) data.get("countnum") == null ? "0"
						: (String) data.get("countnum");
				String filecode = (String) data.get("file_code");
				if ("rqjyxkz_pzqy".equals(tempCode)) {
					if ("zyxzxkjds".equals(filecode)) {
						agreecount_num += Integer.valueOf(countnum);
						form.setJyxkpz_Agreenum(Integer.valueOf(countnum)+form.getJyxkpz_Agreenum());
					} else if ("byxkjds".equals(filecode)){
						form.setNotjyxkpz_num(Integer.valueOf(countnum));
						notAgreecount+=Integer.valueOf(countnum);
					}else if("bzcltzs".equals(filecode)){
						form.setJyxkpz_gznum(Integer.valueOf(countnum));
						gzCount+=Integer.valueOf(countnum);
					}else{
						agreecount_num += Integer.valueOf(countnum);
						form.setJyxkpz_Agreenum(Integer.valueOf(countnum)+form.getJyxkpz_Agreenum());
					}

				} else if ("rqssgd".equals(tempCode)) {
					if ("zyxzxkjds".equals(filecode)) {
						agreecount_num += Integer.valueOf(countnum);
						form.setSsgd_Agreenum(Integer.valueOf(countnum)+form.getSsgd_Agreenum());
					} else if ("byxkjds".equals(filecode)){
						form.setNotssgd_num(Integer.valueOf(countnum));
						notAgreecount+=Integer.valueOf(countnum);
					}else if("bzcltzs".equals(filecode)){
						form.setSsgd_gznum(Integer.valueOf(countnum));
						gzCount+=Integer.valueOf(countnum);
					}else{
						agreecount_num += Integer.valueOf(countnum);
						form.setSsgd_Agreenum(Integer.valueOf(countnum));
					}
					
				} else if ("rqjyxkz_gdqy".equals(tempCode)) {
					if ("zyxzxkjds".equals(filecode)) {
						agreecount_num += Integer.valueOf(countnum);
						form.setJyxkgd_Agreenum(Integer.valueOf(countnum)+form.getJyxkgd_Agreenum());
					} else if ("byxkjds".equals(filecode)){
						form.setNotjyxkgd_num(Integer.valueOf(countnum));
						notAgreecount+=Integer.valueOf(countnum);
					}else if("bzcltzs".equals(filecode)){
						form.setJyxkgd_gznum(Integer.valueOf(countnum));
						gzCount+=Integer.valueOf(countnum);
					}else{
						agreecount_num += Integer.valueOf(countnum);
						form.setJyxkgd_Agreenum(Integer.valueOf(countnum));
					}
				} else if ("rqrsqjazwxqyzz".equals(tempCode)) {
					if ("zyxzxkjds".equals(filecode)) {
						agreecount_num += Integer.valueOf(countnum);
						form.setQyzz_Agreenum(Integer.valueOf(countnum)+form.getQyzz_Agreenum());
					} else if ("byxkjds".equals(filecode)){
						form.setNotqyzz_num(Integer.valueOf(countnum));
						notAgreecount+=Integer.valueOf(countnum);
					}else if("bzcltzs".equals(filecode)){
						form.setQyzz_gznum(Integer.valueOf(countnum));
						gzCount+=Integer.valueOf(countnum);
					}else{
						agreecount_num += Integer.valueOf(countnum);
						form.setQyzz_Agreenum(Integer.valueOf(countnum));
					}

				}

			}
		}
		form.setAgreeCountnum(agreecount_num);
		form.setNotAgreeNum(notAgreecount);
		form.setGzCountnum(gzCount);
		return list;
	}

	private String baseSql(Map map, boolean isTotalState) {
		String starttime=(String) map.get("starttime");
		String endtime=(String) map.get("endtime");
		String type = (String) map.get("overtime");
		StringBuffer sb = new StringBuffer();
		if (isTotalState) {// 统计每种业务的所有案件数
			if (StringUtils.isNotBlank(type)) {// 统计是否超时的数目
				sb.append("select  count(1) as countnum, temp.template_code, \r");
				sb.append("case when proc.end_<=proc.process_duedate then 'notovertime' \r");
				sb.append(" when proc.end_>proc.process_duedate then 'overtime'   else 'notdata' end as timetype from\r");
			} else {
				sb.append("select  count(1) as countnum, temp.template_code from \r");
			}
		} else {
			sb.append("select   temp.template_code,ins.master_entity_key from \r");
		}
		sb.append("JBPM4_HIST_PROCINST proc,WF_BUS_INSTANCE ins,WF_BUS_TEMPLATE temp \r");
		sb.append(" where proc.id_=ins.proc_inst_id  \r");
		sb.append(" and ins.template_id=temp.id    and proc.state_='ended'  \r");
		sb.append(" and temp.template_code in ('rqssgd','rqrsqjazwxqyzz','rqjyxkz_gdqy','rqjyxkz_pzqy')\r");
		if(StringUtils.isNotBlank(starttime)){
			sb.append(" and  ins.bus_date2>=to_date('"+starttime+"','yyyy-MM-dd')");
		}else{
			starttime=DateUtilsEX.getCurYear()+"-01-01";
			sb.append(" and  ins.bus_date2>=to_date('"+starttime+"','yyyy-MM-dd')");
		}
		if(StringUtils.isNotBlank(endtime)){
			sb.append(" and  ins.bus_date2<=to_date('"+endtime+"','yyyy-MM-dd')");
		}else{
			endtime=DateUtilsEX.getCurYear()+"-"+DateUtilsEX.getCurMonth()+"-"+DateUtilsEX.getCurDay();
			sb.append(" and  ins.bus_date2<=to_date('"+endtime+"','yyyy-MM-dd')");
		}
		if (StringUtils.isNotBlank(type)) {
			sb.append("group by case when proc.end_<=proc.process_duedate then 'notovertime' \r");
			sb.append(" when proc.end_>proc.process_duedate then 'overtime'   else 'notdata' end \r");
			sb.append(", temp.template_code \r");
		} else {
			if (isTotalState) {// 统计每种业务的所有案件数
				sb.append("  group by temp.template_code \r");
			}
		}

		return sb.toString();

	}

	

}
